package page;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class InfoDate {
	String user = "root";
	String password = "root";
	String url = "jdbc:mysql://localhost:3306/steel2?useUnicode=true&characterEncoding=utf8";

	private Connection getCon() {
		Connection con = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection(url, user, password);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return con;
	}

	public List<InFo> getInfo(PageTool page) throws ClassNotFoundException,
			SQLException {
		Connection con = getCon();
		PreparedStatement pst = con
				.prepareStatement("select * from supply_demand_info order by supply_demand_id desc limit ?,?");
		page.setRecordCount(getNewsCount());
		pst.setInt(1, page.getRowIndex());
		pst.setInt(2, page.getPageSize());
		ResultSet rs = pst.executeQuery();
		List<InFo> list = new ArrayList<InFo>();
		while (rs.next()) {
			InFo info = new InFo();
			info.setNewsId(rs.getInt("supply_demand_id"));
			info.setTitle(rs.getString("title"));
			list.add(info);
		}
		rs.close();
		pst.close();
		con.close();
		return list;
	}

	public int getNewsCount() throws SQLException {
		Connection con = getCon();
		PreparedStatement pst = con
				.prepareStatement("select count(supply_demand_id) from supply_demand_info");
		ResultSet rs = pst.executeQuery();
		rs.next();
		int count = rs.getInt(1);
		rs.close();
		pst.close();
		con.close();
		return count;
	}

	public static void main(String[] args) throws ClassNotFoundException,
			SQLException {
		PageTool page = new PageTool(20, 2);
		InfoDate info = new InfoDate();
		List<InFo> list = info.getInfo(page);
		System.out.print(page.getRecordCount()+" ");
		System.out.print(page.getPageNo()+" ");
		System.out.println(page.getPageCount());
		for (InFo s : list) {
			System.out.print(s.getNewsId());
			System.out.println(s.getTitle());
		}
		System.out.println("=========");
		page.setPageNo(1826);
		System.out.print(page.getRecordCount()+" ");
		System.out.print(page.getPageNo()+" ");
		System.out.println(page.getPageCount());
		list = info.getInfo(page);
		for (InFo s : list) {
			System.out.print(s.getNewsId());
			System.out.println(s.getTitle());
		}
	}
}
